package ua.kharkov.knure.dereza.summarytask4.db.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import ua.kharkov.knure.dereza.summarytask4.db.dao.StatusDAO;
import ua.kharkov.knure.dereza.summarytask4.db.dto.StatusDTO;

/**
 * Data access object for Card's status
 * 
 * @author dereza
 * 
 */
public class MysqlStatusDAO implements StatusDAO{
	
	private static final Logger log = Logger.getLogger(MysqlStatusDAO.class);

	private Connection conn;

	// ---------------------------------------------
	// SQL queries
	// ---------------------------------------------
	private static final String SQL_FIND_STATUS_BY_ID = "SELECT * FROM statuses WHERE id=?;";
	private static final String SQL_FIND_ID_BY_STATUS_NAME = "SELECT * FROM statuses WHERE name=?;";

	@Override
	public StatusDTO findStatusById(int id) {
		StatusDTO statusDTO = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_FIND_STATUS_BY_ID);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				statusDTO = new StatusDTO();
				statusDTO.setId(rs.getInt(1));
				statusDTO.setName(rs.getString(2));
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, pstmt, conn);
		}
		return statusDTO;
	}
    
	@Override
	public StatusDTO findStatusByName(String name) {
		StatusDTO statusDTO = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_FIND_ID_BY_STATUS_NAME);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				statusDTO = new StatusDTO();
				statusDTO.setId(rs.getInt(1));
				statusDTO.setName(rs.getString(2));
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, pstmt, conn);
		}
		return statusDTO;
	}
	
	@Override
	public void commitAndClose(ResultSet rs, Statement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				log.error("Cannot close result set", e);
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				log.error("Cannot close statement", e);
			}
		}
		if (conn != null) {
			try {
				conn.commit();
				conn.close();
			} catch (SQLException e) {
				log.error("Cannot commit and close connection", e);
			}
		}
	}

    @Override
	public void rollback(Connection conn) {
		if (conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				log.error("Cannot rollback", e);
			}
		}
	}
}